/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facades;

import entities.Candidate;
import entities.Search;
import java.util.ArrayList;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author damfercer
 */
@Stateless
public class SearchFacade extends AbstractFacade<Search> {
    @PersistenceContext(unitName = "ProyectoPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public SearchFacade() {
        super(Search.class);
    }
    
    public List<Search> getAllPublicSearchs(){
        Query namedQuery=em.createNamedQuery("Search.findByIsPublicAndActive");
        namedQuery.setParameter("isPublic", true);
        namedQuery.setParameter("active", true);
        return namedQuery.getResultList();
    }
    
    public boolean candidateInASearch(Candidate candidate, String sql){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT EXISTS "
                +"(SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
                sb.append("AND candidate.ID_CANDIDATE=");
                sb.append(candidate.getIdCandidate());
                sb.append(" ");
                sb.append(sql);
                sb.append(" )");
                Query searchCandidatesQuery=em.createNativeQuery(sb.toString());
                long longNum=(Long)searchCandidatesQuery.getSingleResult();
        return ((int)longNum)>0;
    }
    

    
}
